﻿#include "setUp.h"

//QList<TcpClient*> clientList;
QMap<QString,TcpClient*> clientList;
QMap<QString,SerialportClient*> serialPortClientList;

QString dataopath;
int autodo = 0;

/**
 * @brief 构造函数
 */
SetUp::SetUp()
{

}

/**
 * @brief 析构函数
 */
SetUp::~SetUp()
{}


Q_INVOKABLE void SetUp::printClientList(){

    qDebug() << "Print ClientList ======";
    foreach(auto client, clientList){
        if(client){
            qDebug() << "Client-equId:" << client->equId;
        }else{
            qDebug() << "PrintFalse > Client is null";
        }
    }
    qDebug() << "Print ClientList end ======";
}

//添加设备接口
Q_INVOKABLE  int SetUp::addNewEqu(QVariantMap info)
{
    QSqlDatabase db=dbHelper::openConnection();
    QSqlQuery query(db);
    QString id = info["Id"].toString();

    //Id为空,查询最大值当新Id
    if(id.isNull()){
        id=common->GetGuid();
        // 随机生成Id后，返回设备的顺序根据Id排序不固定，这里修改为生成递增的数据 - jnn
        QString sql = QString("Select Max(Id) from EquipmentInfo");
        query.exec(sql);
        while(query.next()){
            id = QString("%1").arg(query.value("Max(Id)").toInt()+1, 10, 10, QLatin1Char('0'));
            qDebug() << "新生成的Id" << id;
        }

    }

    QString status;
    //网口连接,判断Ip是否重复
    if(info["EquConnectType"].toString()=="0"){
        QString sql=QString("Select Count(*) As Num From EquipmentInfo Where EquConnectType='0' and Ip='%1' and id<>'%2'").arg(info["Ip"].toString(),id);
        query.exec(sql);
        bool exist;
        while (query.next()) {
            exist=query.value("Num").toInt()>0;
        }
        if(exist){
            return 2;
        }

        if(clientList.contains(id)){
            clientList[id]->equId = id;
            clientList[id]->ClientConnectToHost(info["Ip"].toString(),info["Port"].toInt());
        }else{
            TcpClient *client=new TcpClient();
            client->equId = id;
            client->ClientConnectToHost(info["Ip"].toString(),info["Port"].toInt());
            clientList.insert(id,client);

            QString sql=QString("Insert into StaticNumInfo Values('%1',0,0,0,0)").arg(id);
            query.exec(sql);
        }
        if(serialPortClientList.contains(id)){
            serialPortClientList.remove(id);
        }
        thread()->msleep(500);

        status=clientList[id]->IsOnline()?"1":"0";//0:脱机 1:已连接 2:运行中 3:已完成 4:故障
    }
    else if(info["EquConnectType"].toString()=="1")
    {
        QString sql=QString("Select Count(*) As Num From EquipmentInfo Where EquConnectType='1' and SerialPort='%1' and id<>'%2'").arg(info["SerialPort"].toString(),id);
        query.exec(sql);
        bool exist;
        while (query.next()) {
            exist=query.value("Num").toInt()>0;
        }
        if(exist){
            return 2;
        }

        if(serialPortClientList.contains(id)){
            serialPortClientList[id]->equId = id;
            serialPortClientList[id]->ClientConnectToHost(info["SerialPort"].toString(),info["BaudRate"].toString());
        }else{
            SerialportClient *client=new SerialportClient();
            client->equId = id;
            client->ClientConnectToHost(info["SerialPort"].toString(),info["BaudRate"].toString());
            serialPortClientList.insert(id,client);

            QString sql=QString("Insert into StaticNumInfo Values('%1',0,0,0,0)").arg(id);
            query.exec(sql);
        }
        if(clientList.contains(id)){
            clientList.remove(id);
        }
        thread()->msleep(500);

        status=serialPortClientList[id]->IsOnline()?"1":"0"; //0:脱机 1:已连接 2:运行中 3:已完成 4:故障

    }

    QString sql=QString("Insert Or Replace into EquipmentInfo values('%1','%2','%3','%4','%5','%6','%7','%8','')").arg
            (id,info["EquName"].toString(),status,info["EquConnectType"].toString(),
            info["Ip"].toString(),info["Port"].toString(),info["SerialPort"].toString(),info["BaudRate"].toString());
    query.exec(sql);
    //    printClientList();

    return  status.toInt();
}

//查询设备接口
Q_INVOKABLE  QVariantMap SetUp::queryEquInfo(){
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString sql="Select * From EquipmentInfo";
    query.exec(sql);
    QVariantMap result;
    while (query.next()) {
        QVariantMap map;
        for (int i = 0; i < query.record().count(); ++i) {
            map[query.record().fieldName(i)] = query.value(i);
        }
        result[query.value("Id").toString()] = map;
    }
    return result;
}

// 获取除了未连接状态以外的设备
Q_INVOKABLE  QVariantMap SetUp::queryEquInfoConnect(){
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString sql=QString("Select * From EquipmentInfo ");
    query.exec(sql);
    QVariantMap result;
    while (query.next()) {
        QVariantMap map;
        for (int i = 0; i < query.record().count(); ++i) {
            map[query.record().fieldName(i)] = query.value(i);
        }
        result[query.value("Id").toString()] = map;
    }
    return  result;
}

//根据状态,查询设备
Q_INVOKABLE  QVariantList SetUp::queryEquInfoByStatus(QString equStatus){
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString sql=QString("Select * From EquipmentInfo Where EquStatus='%1'").arg(equStatus);
    query.exec(sql);
    QVariantList result;
    while (query.next()) {
        QVariantMap map;
        for (int i = 0; i < query.record().count(); ++i) {
            map[query.record().fieldName(i)] = query.value(i);
        }
        result.append(map);
    }
    return  result;
}

//查询设备详细信息
Q_INVOKABLE  QVariantMap SetUp::querySingleEquInfo(QString id ){
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString sql=QString("Select * From EquipmentInfo Where Id='%1'").arg(id);
    query.exec(sql);
    QVariantMap map;

    while (query.next()) {
        for (int i = 0; i < query.record().count(); ++i) {
            map[query.record().fieldName(i)] = query.value(i);
        }
    }
    return  map;
}

//删除设备接口
Q_INVOKABLE  int SetUp::delEquInfo(QString id)
{
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString sql=QString("Delete From EquipmentInfo Where id='%1'").arg(id);
    query.exec(sql);
    sql=QString("Delete From StaticNumInfo Where EquGuid='%1'").arg(id);
    bool res=query.exec(sql);
    clientList.remove(id);
    return res?0:1;
}

//连接设备
Q_INVOKABLE int SetUp::connectEqu(QString id){
    QSqlDatabase db=dbHelper::openConnection();
    QSqlQuery query(db);
    QString sql=QString("Select * From EquipmentInfo Where Id='%1'").arg(id);
    query.exec(sql);
    QString status="0";

    while (query.next()) {
        if(query.value("EquConnectType").toString()=="0"){
            if(clientList.contains(id)){
                clientList[id]->equId = id;
                clientList[id]->ClientConnectToHost(query.value("Ip").toString(),query.value("Port").toInt());
                thread()->msleep(500);
            }else{
                TcpClient *client=new TcpClient();
                client->equId = id;
                client->ClientConnectToHost(query.value("Ip").toString(),query.value("Port").toInt());
                thread()->msleep(500);
                clientList.insert(id,client);
            }
            if(serialPortClientList.contains(id)){
                serialPortClientList.remove(id);
            }
            status=clientList[id]->IsOnline()?"1":"0"; //0:脱机 1:已连接 2:运行中 3:已完成 4:故障
        }
        else if(query.value("EquConnectType").toString()=="1")
        {
            if(serialPortClientList.contains(id)){
                serialPortClientList[id]->equId = id;
                serialPortClientList[id]->ClientConnectToHost(query.value("SerialPort").toString(),query.value("BaudRate").toString());
            }else{
                SerialportClient *client=new SerialportClient();
                client->equId = id;
                client->ClientConnectToHost(query.value("SerialPort").toString(),query.value("BaudRate").toString());
                serialPortClientList.insert(id,client);
            }
            if(clientList.contains(id)){
                clientList.remove(id);
            }
            status=serialPortClientList[id]->IsOnline()?"1":"0"; //0:脱机 1:已连接 2:运行中 3:已完成 4:故障
        }
    }

    sql=QString("Update EquipmentInfo Set EquStatus='%1' Where Id='%2'").arg(status,id);
    bool res = query.exec(sql);
    LogHelperJ::write(sql + (res?"[true]":"[false]"));
    return status.toInt();
}

//统计设备数量
Q_INVOKABLE  QList<QString> SetUp::queryEquNum(){
    QSqlDatabase db=dbHelper::openConnection();
    QSqlQuery query(db);
    QString sql="Select equStatus,Count(id) as Num From EquipmentInfo Group By EquStatus Order By EquStatus";
    query.exec(sql);
    QList<QString> result;
    result<<"0"<<"0"<<"0"<<"0"<<"0"<<"0";
    int total=0;
    while (query.next()) {
        total+=query.value("Num").toInt();

        result.replace(query.value("EquStatus").toInt()+1,query.value("Num").toString());
    }
    result.replace(0,QString::number(total));
    return  result;
}

//获取串口号
Q_INVOKABLE QList<QString> SetUp::querySerialPort(){
    QList<QString> result;
    foreach (const QSerialPortInfo &info,QSerialPortInfo::availablePorts()) {
        result.append(info.portName());
    }
    return  result;
}

//自动连接所有设备
void SetUp::connectAll(){
    clientList.clear();
    QSqlDatabase db=dbHelper::openConnection();
    QSqlQuery query(db);
    QString sql="Select * From EquipmentInfo order by Id";
    query.exec(sql);
    QVector<QString> idList;
    while(query.next()){
        idList.push_back(query.value("ID").toString());
    }
    db.close();
    foreach(QString id, idList){
        connectEqu(id);
    }

    // 检查connect之后数据库的状态
    db.open();
    sql = "Select * From EquipmentInfo";
    query.exec(sql);
}


// 前端读数据库路径
Q_INVOKABLE QString SetUp::readDefPath()
{
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString filePath;
    QString sql="SELECT Name FROM ItemCode WHERE DicCode=100";
    query.exec(sql);
    while (query.next()) {
        QString column1Value = query.value("Name").toString();
        filePath=column1Value;
    }
    dataopath=filePath;
    return filePath;
}

Q_INVOKABLE void SetUp::choosePath()
{
    QString pafl = QFileDialog::getExistingDirectory(nullptr, "选择文件夹", "/path/to/default/directory");
    if(!pafl.isNull()){
        fpath = pafl;
    }else{
        fpath = dataopath;
    }
}

QString SetUp::readPath()
{
    return fpath;
}
// 前端写数据库路径 0 写入成功 1 写入失败
Q_INVOKABLE int SetUp::writeDefPath()
{
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString lineEditText = fpath;
    lineEditText.replace("\\", "\\\\");
    if(lineEditText.isNull()){
        return 0;
    }
    QString sql=QString("Update ItemCode set Name='%1' Where DicCode='%2'").arg(lineEditText,"100");
    if(query.exec(sql)){
        dataopath = fpath;
        return 1;
    }else{
        return 0;
    }
}
// 自动导出状态设置
Q_INVOKABLE void SetUp::autoDout(int status)
{
    autostatus = status;
    autodo =  status;
    QMessageBox::information(nullptr,"提示","保存成功");
    return;
}
// 查询
Q_INVOKABLE QVariantList SetUp::refTableData(QString comName)
{
    QString dicCode;
    if(comName=="样本名称"){
        dicCode = "1";
    }else if(comName=="样本类型"){
        dicCode = "2";
    }else if(comName=="扩增体系(μl)"){
        dicCode = "4";
    }else if(comName=="耗材类型"){
        dicCode = "6";
    }else if(comName=="试剂盒"){
        dicCode = "12";
    }else if(comName=="热盖温度(℃)"){
        dicCode = "20";
    }else if(comName=="通道一"){
        dicCode = "7";
    }else if(comName=="通道二"){
        dicCode = "8";
    }else if(comName=="通道三"){
        dicCode = "9";
    }else if(comName=="通道四"){
        dicCode = "10";
    }else if(comName=="内标"){
        dicCode = "11";
    }else if(comName=="基因分析仪"){
        dicCode = "14";
    }else if(comName=="试剂盒类型"){
        dicCode = "15";
    }else if(comName=="体系(μl)"){
        dicCode = "19";
    }else{
        QString info=comName;
        QString type=  info.left(info.length()-3);
        QSqlDatabase db = dbHelper::openConnection();
        QSqlQuery query(db);

        QString sql=QString("Select Code From ItemCode Where Name='%1'").arg(type);
        query.exec(sql);
        while (query.next()) {
            dicCode=query.value("Code").toString();
        }
    }

    if(dicCode=="4"){
        QSqlDatabase db = dbHelper::openConnection();
        QSqlQuery query(db);
        QString sql=QString("delete  FROM ItemCode WHERE DicCode='%1' and Name=" " ").arg(dicCode);
        query.exec(sql);
    }
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);

    QString sql=QString("SELECT Code,Name,IsDefault FROM ItemCode WHERE DicCode='%1'").arg(dicCode);
    QStringList codes,sanames,isds;
    query.exec(sql);
    QString codeval,nameval,isdval;
    QStringList rowdata;
    QVariantList allval;
    while (query.next()) {
        rowdata.clear();
        QString column1Value=query.value("Code").toString();
        QString column2Value = query.value("Name").toString();
        QString column3Value = query.value("IsDefault").toString();
        rowdata<<column1Value<<column2Value<<column3Value;
        allval.append(rowdata);
    }
    return allval;
}

// 获取字段默认值
Q_INVOKABLE QString SetUp::getDefaultData(QString comName){
    QString res;
    qDebug() << comName;
    QVariantList list = refTableData(comName);
    foreach(QVariant line, list){
        QStringList lineList = line.value<QStringList>();
        if(lineList.size() < 3) continue;
        if(lineList[2]=="1"){
            res = lineList[1];
        }
    }
    return res;
}

// 添加
Q_INVOKABLE int SetUp::addTableData(QString comName, QString value)
{
    QString dicCode;
    if(comName=="样本名称"){
        dicCode = "1";
    }else if(comName=="样本类型"){
        dicCode = "2";
    }else if(comName=="扩增体系(μl)"){
        dicCode = "4";
    }else if(comName=="耗材类型"){
        dicCode = "6";
    }else if(comName=="试剂盒"){
        dicCode = "12";
    }else if(comName=="通道一"){
        dicCode = "7";
    }else if(comName=="通道二"){
        dicCode = "8";
    }else if(comName=="通道三"){
        dicCode = "9";
    }else if(comName=="通道四"){
        dicCode = "10";
    }else if(comName=="内标"){
        dicCode = "11";
    }else if(comName=="基因分析仪"){
        dicCode = "14";
    }else if(comName=="试剂盒类型"){
        dicCode = "15";
    }else if(comName=="体系(μl)"){
        dicCode = "19";
    }else{
        QString info=comName;
        QString type=  info.left(info.length()-3);
        QSqlDatabase db = dbHelper::openConnection();
        QSqlQuery query(db);

        QString sql=QString("Select Code From ItemCode Where Name='%1'").arg(type);
        query.exec(sql);
        while (query.next()) {
            dicCode=query.value("Code").toString();
        }
    }


    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString sql=QString("SELECT Code FROM ItemCode WHERE DicCode='%1'").arg(dicCode);
    QStringList codes;
    query.exec(sql);
    while (query.next()) {
        QString column1Value=query.value("Code").toString();
        codes<<column1Value;
    }
    int code = 0;
    for(int i=1;i<codes.length()+1;i++){
        QString codest = QString::number(i);
        if(!codes.contains(codest)){
            code = codest.toInt();
            break;
        }
    }
    if(dicCode=="7"||dicCode=="8"||dicCode=="9"||dicCode=="10"){
        QSqlDatabase db = dbHelper::openConnection();
        QSqlQuery query(db);
        QString sql=QString("Insert into ItemCode values('%1','%2','%3','0')").arg("11",dicCode+QString::number(code),value);
        query.exec(sql);
        sql=QString("Insert into ItemCode values('%1','%2','%3','0')").arg(dicCode,QString::number(code),value);
        query.exec(sql);
    }else if(dicCode=="15"){
        QString sql=QString("Insert into ItemCode values('%1',"
                            "(Select case "
                            "when  max(DicCode)+1==100 then 101 else  max(DicCode)+1  end as code "
                            "From ItemCode where DicCode<>'100'),'','0')").arg(dicCode);
        query.exec(sql);

        sql=QString("Insert into ItemCode values("
                    "(Select case "
                    "when  max(DicCode)+1==100 then 101 else  max(DicCode)+1  end as code "
                    "From ItemCode where DicCode<>'100'),'','初始试剂盒','0')").arg(dicCode);
        query.exec(sql);
    }else{
        QString sql=QString("Insert into ItemCode values('%1','%2','%3','0')").arg(dicCode,QString::number(code),value);
        query.exec(sql);
    }
    return 1;
}
// 默认
Q_INVOKABLE int SetUp::setTableData(QString comName, QString code)
{
    QString dicCode;
    if(comName=="样本名称"){
        dicCode = "1";
    }else if(comName=="样本类型"){
        dicCode = "2";
    }else if(comName=="扩增体系(μl)"){
        dicCode = "4";
    }else if(comName=="耗材类型"){
        dicCode = "6";
    }else if(comName=="试剂盒"){
        dicCode = "12";
    }else if(comName=="通道一"){
        dicCode = "7";
    }else if(comName=="通道二"){
        dicCode = "8";
    }else if(comName=="通道三"){
        dicCode = "9";
    }else if(comName=="通道四"){
        dicCode = "10";
    }else if(comName=="内标"){
        dicCode = "11";
    }else if(comName=="基因分析仪"){
        dicCode = "14";
    }else if(comName=="试剂盒类型"){
        dicCode = "15";
    }else if(comName=="体系(μl)"){
        dicCode = "19";
    }else{
        QString info=comName;
        QString type=  info.left(info.length()-3);
        QSqlDatabase db = dbHelper::openConnection();
        QSqlQuery query(db);

        QString sql=QString("Select Code From ItemCode Where Name='%1'").arg(type);
        query.exec(sql);
        while (query.next()) {
            dicCode=query.value("Code").toString();
        }
    }

    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);

    QString sql=QString("SELECT Name FROM ItemCode WHERE DicCode='%1' and IsDefault ='1' ").arg(dicCode);
    QString isname;
    query.exec(sql);
    while (query.next()) {
        QString column2Value = query.value("Name").toString();
        isname.append(column2Value);
    }

    sql=QString("UPDATE ItemCode SET IsDefault =0 WHERE DicCode='%1'").arg(dicCode);
    query.exec(sql);
    if(dicCode == "11"&&code==isname){
        sql=QString("UPDATE ItemCode SET IsDefault ='0' WHERE DicCode='%1' and Code= '%2'").arg(dicCode,code);
        query.exec(sql);
    }else{
    }
    sql=QString("UPDATE ItemCode SET IsDefault ='1' WHERE DicCode='%1' and Code= '%2'").arg(dicCode,code);
    query.exec(sql);
    return 1;
}
// 删除
Q_INVOKABLE int SetUp::delTableData(QString comName, QString code)
{
    QString dicCode;
    if(comName=="样本名称"){
        dicCode = "1";
    }else if(comName=="样本类型"){
        dicCode = "2";
    }else if(comName=="扩增体系(μl)"){
        dicCode = "4";
    }else if(comName=="耗材类型"){
        dicCode = "6";
    }else if(comName=="试剂盒"){
        dicCode = "12";
    }else if(comName=="通道一"){
        dicCode = "7";
    }else if(comName=="通道二"){
        dicCode = "8";
    }else if(comName=="通道三"){
        dicCode = "9";
    }else if(comName=="通道四"){
        dicCode = "10";
    }else if(comName=="内标"){
        dicCode = "11";
    }else if(comName=="基因分析仪"){
        dicCode = "14";
    }else if(comName=="试剂盒类型"){
        dicCode = "15";
    }else if(comName=="体系(μl)"){
        dicCode = "19";
    }else{
        QString info=comName;
        QString type=  info.left(info.length()-3);
        QSqlDatabase db = dbHelper::openConnection();
        QSqlQuery query(db);

        QString sql=QString("Select Code From ItemCode Where Name='%1'").arg(type);
        query.exec(sql);
        while (query.next()) {
            dicCode=query.value("Code").toString();
        }
    }
    QString id =code;

    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);

    QString sql=QString("SELECT IsDefault FROM ItemCode WHERE DicCode=4 and Code='%1'").arg(id);
    query.exec(sql);

    bool isDefault=false;
    while (query.next()) {
        isDefault = query.value("IsDefault").toString() =="1";
    }
    sql=QString("SELECT Name FROM ItemCode WHERE DicCode='%1'").arg(dicCode);
    QStringList sanames;
    query.exec(sql);
    while (query.next()) {
        QString column2Value = query.value("Name").toString();
        sanames.append(column2Value);
    }
    if(sanames.length()==1){
        return 1;
    }
    sql=QString("DELETE FROM ItemCode WHERE Code = '%1' and DicCode = '%2'").arg(id,dicCode);
    query.exec(sql);
    if(dicCode=="7"||dicCode=="8"||dicCode=="9"||dicCode=="10"){
        sql=QString("DELETE FROM ItemCode WHERE Code = '%1' and DicCode = '%2'").arg(id,"11");
        query.exec(sql);
    }
    return 1;

}
// 修改
Q_INVOKABLE int SetUp::updataTableData(QString comName, QString value, QString code){
    QString dicCode;
    if(comName=="样本名称"){
        dicCode = "1";
    }else if(comName=="样本类型"){
        dicCode = "2";
    }else if(comName=="扩增体系(μl)"){
        dicCode = "4";
    }else if(comName=="耗材类型"){
        dicCode = "6";
    }else if(comName=="试剂盒"){
        dicCode = "12";
    }else if(comName=="通道一"){
        dicCode = "7";
    }else if(comName=="通道二"){
        dicCode = "8";
    }else if(comName=="通道三"){
        dicCode = "9";
    }else if(comName=="通道四"){
        dicCode = "10";
    }else if(comName=="内标"){
        dicCode = "11";
    }else if(comName=="基因分析仪"){
        dicCode = "14";
    }else if(comName=="试剂盒类型"){
        dicCode = "15";
    }else if(comName=="体系(μl)"){
        dicCode = "19";
    }else{
        QString info=comName;
        QString type=  info.left(info.length()-3);
        QSqlDatabase db = dbHelper::openConnection();
        QSqlQuery query(db);

        QString sql=QString("Select Code From ItemCode Where Name='%1'").arg(type);
        query.exec(sql);
        while (query.next()) {
            dicCode=query.value("Code").toString();
        }
    }
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString infoname=value;

    QString sql=QString("Update ItemCode set Name='%1' Where DicCode='%2' and Code='%3'").arg(infoname,dicCode,code);
    query.exec(sql);
    if(dicCode=="7"||dicCode=="8"||dicCode=="9"||dicCode=="10"){
        QString sql=QString("Update ItemCode set Name='%1' Where DicCode='%2' and Code='%3'").arg(infoname,"11",dicCode+code);
        query.exec(sql);
    }
    return 1;
}


Q_INVOKABLE QVariantList SetUp::getDefaultChannel(){

}

// 实验恢复
Q_INVOKABLE int SetUp::hfTableData(QString eqname,QString exename)
{

    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);

    // 查看当前设备是否可以进行实验恢复
    QString sql = QString("Select EquStatus From EquipmentInfo Where EquName='%1'").arg(eqname);
    query.exec(sql);
    while(query.next()){
        int status = query.value("EquStatus").toInt();
        if(status == 0 || status == 2 || status == 4){   // 0:未连接 1:在线 2:运行中 3:已完成 4:故障
            return status;
        }
    }


    sql="delete from FluorescenceData ;";
    query.exec(sql);

    sql = QString("Select Id from EquipmentInfo where EquName='%1' ").arg(eqname);
    query.exec(sql);
    QString index;
    while (query.next()) {
        index=query.value("Id").toString();
    }
    TcpClient *hfClient = clientList.value(index);
    sql = QString("Select Id from ExperimentInfo where EquName='%1' and ExperimentName='%2' ").arg(eqname,exename);
    QString id;
    query.exec(sql);
    while (query.next()) {
        id=query.value("Id").toString();
    }

    sql=QString("Select CycleNum from AmplStep where ProgramId=(Select AmplGuid from ExperimentInfo where Id = '%1' ) and "
                "CASE WHEN "
                "(Select count(*) from AmplStep where ProgramId=(Select AmplGuid from ExperimentInfo) and IsCollect='是' where Id = '%1')>0 "
                "then "
                "IsCollect='是' "
                "else "
                "StepNum=(Select max(StepNum) from AmplStep where programId=(Select AmplGuid from ExperimentInfo where Id = '%1')) "
                "end").arg(id);
    query.exec(sql);
    while (query.next()) {
        xhval=query.value("CycleNum").toInt();
    }
    qDebug()<<xhval;
    timSend->setInterval(2000);
    connect(timSend,&QTimer::timeout,this,[=]()
    {
        if(syhftime<=xhval){
            QString type = "0007";
            QString type2 = QString("%1").arg(syhftime, 4, 16, QLatin1Char('0'));
            QString end2 = "0000";
            int len = 8;
            QString len1 = QString("%1").arg(len, 4, 16, QLatin1Char('0'));
            QString data = "6200003D"+len1+type+type2+end2+"55AA";
            QString finalData;
            for (int i = 0; i < data.length(); ++i) {
                finalData.append(data.at(i));
                // 如果不是字符串的最后一个字符，并且i是偶数（从0开始），则添加一个空格
                if ((i+1)%2==0) {
                    finalData.append(' ');
                }
            }
            QByteArray finalInfo=common->GetSendData(finalData);
            hfClient->ClientSendingData(finalInfo);
            qDebug()<<xhval<<syhftime;
            emit syhfn(QString::number(syhftime/xhval,'f',2).toDouble());
            syhftime++;
        }else if(syhftime>xhval){
            //            QMessageBox::information(NULL, "提示", "恢复成功!");
            syhftime=1;
            disconnect(timSend,0,0,0);
            emit syhfn(1);
            emit hfcg();
            return 1;
        }
    });
    timSend->start();
    return 1;
}
// 停止恢复
Q_INVOKABLE int SetUp::tzhf(){
    QMessageBox::information(NULL, "提示", "停止恢复!");
    syhftime=1;
    disconnect(timSend,0,0,0);
    return 1;
}

Q_INVOKABLE int SetUp::importModel(){
    QStringList sFilePath;

    QFileDialog *fDialog=new QFileDialog(nullptr,"file dlg","/home","Excel files(*.xlsx)");
    if(fDialog->exec()==QFileDialog::Accepted)
    {
        sFilePath= fDialog->selectedFiles();
    }

    if(sFilePath.length()>0){
        QXlsx::Document xlsx(sFilePath[0]);
        QStringList sheetList = xlsx.sheetNames();

        QSqlDatabase db = dbHelper::openConnection();
        QSqlQuery query(db);

        db.transaction();
        for (int i=0;i<sheetList.count();i++) {
            xlsx.selectSheet(sheetList[i]);
            QXlsx::Worksheet *worksheet = xlsx.currentWorksheet();

            QString jyfxyq = worksheet->read("A3").toString();//基因分析仪
            QString reagentKitType =worksheet->read("B3").toString();//试剂盒类型
            QString reagentKitName =worksheet->read("C3").toString();//试剂盒名称
            QString system =worksheet->read("D3").toString();//体系
            QString memo =worksheet->read("E3").toString();//备注

            QString sql=QString("Select Name From ItemCode Where DicCode=(Select Code From ItemCode Where Name='%1')").arg(reagentKitType);
            query.exec(sql);
            bool canImport=false;
            while (query.next()) {
                if(query.value("Name").toString()==reagentKitName){
                    canImport=true;
                }
            }

            if(canImport){
                QString configGuid="";

                int num;
                sql=QString("Select Count(*) as Num From DataAnalyseConfig Where GeneticAnalysers='%1' and ReagentKitType='%2' and ReagentKitName='%3' and System='%4' ").arg(jyfxyq,reagentKitType,reagentKitName,system);
                query.exec(sql);
                while (query.next()) {
                    num=query.value("Num").toInt();
                }

                if(num>0)
                {
                    QString oldModelGuid;
                    sql=QString("Select ID,ModelGuid From DataAnalyseConfig Where GeneticAnalysers='%1' and ReagentKitType='%2' and ReagentKitName='%3' and System='%4' ").arg(jyfxyq,reagentKitType,reagentKitName,system);
                    query.exec(sql);
                    while (query.next())
                    {
                        configGuid=query.value("ID").toString();
                        oldModelGuid=query.value("ModelGuid").toString();
                    }

                    sql=QString("Delete From ModelConfigurationNormal Where ID='%1'").arg(oldModelGuid);
                    query.exec(sql);

                    sql=QString("Delete From ModelConfigurationY Where ID='%1'").arg(oldModelGuid);
                    query.exec(sql);

                    sql=QString("Delete From ModelConfigurationMini Where ID='%1'").arg(oldModelGuid);
                    query.exec(sql);
                }
                else
                {
                    configGuid =common->GetGuid();
                }

                if(reagentKitType=="常STR"){
                    QString modelGuid=common->GetGuid();
                    sql=QString("Insert into ModelConfigurationNormal Values('%1','%2','%3','%4')").arg(modelGuid,"0","检测方案",worksheet->read("F3").toString());
                    query.exec(sql);
                    sql=QString("Insert into ModelConfigurationNormal Values('%1','%2','%3','%4')").arg(modelGuid,"0","备注",worksheet->read("G3").toString());
                    query.exec(sql);

                    for (int m=5;m<24;m++) {
                        sql=QString("Insert into ModelConfigurationNormal Values('%1','%2','%3','%4')").arg(modelGuid,"1",worksheet->read("A"+QString::number(m)).toString(),worksheet->read("B"+QString::number(m)).toString());
                        query.exec(sql);
                    }

                    QString createTime=common->GetDateTimeStr();
                    sql=QString("Insert or replace into DataAnalyseConfig Values('%1','%2','%3','%4','%5','%6','%7','%8')").arg(configGuid,jyfxyq,reagentKitType,reagentKitName,system,modelGuid,createTime,memo);
                    query.exec(sql);
                }
                else if(reagentKitType=="Y-STR"){

                    QString modelGuid=common->GetGuid();

                    for (int m=5;m<20;m++) {
                        sql=QString("Insert into ModelConfigurationY Values('%1','%2','%3')").arg(modelGuid,worksheet->read("A"+QString::number(m)).toString(),worksheet->read("B"+QString::number(m)).toString());
                        query.exec(sql);
                    }

                    QString createTime=common->GetDateTimeStr();
                    sql=QString("Insert or replace into DataAnalyseConfig Values('%1','%2','%3','%4','%5','%6','%7','%8')").arg(configGuid,jyfxyq,reagentKitType,reagentKitName,system,modelGuid,createTime,memo);

                    query.exec(sql);

                }else if(reagentKitType=="Mini-STR"){

                    QString modelGuid=common->GetGuid();

                    for (int m=5;m<18;m++) {
                        sql=QString("Insert into ModelConfigurationMini Values('%1','%2','%3')").arg(modelGuid,worksheet->read("A"+QString::number(m)).toString(),worksheet->read("B"+QString::number(m)).toString());
                        query.exec(sql);
                    }

                    QString createTime=common->GetDateTimeStr();
                    sql=QString("Insert or replace into DataAnalyseConfig Values('%1','%2','%3','%4','%5','%6','%7','%8')").arg(configGuid,jyfxyq,reagentKitType,reagentKitName,system,modelGuid,createTime,memo);
                    query.exec(sql);
                }
            }
        }
        db.commit();
        return 1;
    }else{
        return 0;
    }
}

Q_INVOKABLE int SetUp::delModel(QString info){

    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QList<QString> idList=info.split(',');

    for (int i=0;i<idList.length();i++) {
        QString sql=QString("Delete From ModelConfigurationNormal where id=(Select ModelGuid From DataAnalyseConfig where ID='%1')").arg(idList[i]);
        query.exec(sql);

        sql=QString("Delete From ModelConfigurationMini where id=(Select ModelGuid From DataAnalyseConfig where ID='%1')").arg(idList[i]);
        query.exec(sql);

        sql=QString("Delete From ModelConfigurationY where id=(Select ModelGuid From DataAnalyseConfig where ID='%1')").arg(idList[i]);
        query.exec(sql);

        sql=QString("Delete From DataAnalyseConfig where Id='%1'").arg(idList[i]);
        query.exec(sql);
    }
}

Q_INVOKABLE QVariantList SetUp::queryModelInfo(QVariantMap ruleInfo){
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);

    QString querySql;
    if(!ruleInfo["Jyfxy"].toString().contains("全部"))
    {
        QList<QString> queryList=ruleInfo["Jyfxy"].toString().split(',');
        QString queryInfo;
        for (int i=0;i<queryList.length();i++)
        {
            queryInfo+="'"+queryList[i]+"',";
        }
        queryInfo = queryInfo.left(queryInfo.length()-1);

        querySql+=QString("and GeneticAnalysers in (%1) ").arg(queryInfo);
    }

    if(!ruleInfo["ReagentKitType"].toString().contains("全部"))
    {
        QList<QString> queryList=ruleInfo["ReagentKitType"].toString().split(',');
        QString queryInfo;
        for (int i=0;i<queryList.length();i++)
        {
            queryInfo+="'"+queryList[i]+"',";
        }
        queryInfo = queryInfo.left(queryInfo.length()-1);


        querySql+=QString("and ReagentKitType in (%1) ").arg(queryInfo);

    }

    if(!ruleInfo["ReagentKitName"].toString().contains("全部"))
    {
        QList<QString> queryList=ruleInfo["ReagentKitName"].toString().split(',');
        QString queryInfo;
        for (int i=0;i<queryList.length();i++)
        {
            queryInfo+="'"+queryList[i]+"',";
        }
        queryInfo = queryInfo.left(queryInfo.length()-1);

        querySql+=QString("and ReagentKitName in (%1) ").arg(queryInfo);

    }

    if(!ruleInfo["System"].toString().contains("全部"))
    {
        QList<QString> queryList=ruleInfo["System"].toString().split(',');
        QString queryInfo;
        for (int i=0;i<queryList.length();i++)
        {
            queryInfo+="'"+queryList[i]+"',";
        }
        queryInfo = queryInfo.left(queryInfo.length()-1);

        querySql+=QString("and System in (%1) ").arg(queryInfo);

    }

    if(!ruleInfo["starttime"].isNull()){
        querySql+=QString("and date(CreateTime) >=date('%1') ").arg(ruleInfo["starttime"].toString());
    }

    if(!ruleInfo["endtime"].isNull()){
        querySql+=QString("and date(CreateTime) <=('%1') ").arg(ruleInfo["endtime"].toString());
    }

    QString sql=QString("Select * From DataAnalyseConfig "
                        "Where 1=1 %1 ").arg(querySql);
    query.exec(sql);

    QVariantList result;
    while (query.next())
    {
        QVariantMap map;
        for (int i = 0; i < query.record().count(); ++i) {
            map[query.record().fieldName(i)] = query.value(i);
        }
        result.append(map);
    }
    return  result;
}

Q_INVOKABLE QVariantList SetUp::getHhpd(){
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);

    QString sql="Select * From MixedJudgment ";
    query.exec(sql);

    QVariantList result;
    while (query.next())
    {
        QVariantMap map;
        for (int i = 0; i < query.record().count(); ++i) {
            map[query.record().fieldName(i)] = query.value(i);
        }
        result.append(map);
    }
    qDebug() << result;
    return  result;
}

Q_INVOKABLE int SetUp::editHhpd(QVariantList info){
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString sql="Delete From MixedJudgment";
    query.exec(sql);
    foreach(QVariant program,info){
        QVariantMap map=program.value<QVariantMap>();

        QString Parameter=map.value("Parameter").toString();
        QString Num=map.value("Num").toString();

        sql=QString("Insert into MixedJudgment Values('%1','%2')").arg(Parameter,Num);
        query.exec(sql);
    }
}


Q_INVOKABLE QVariantList SetUp::getStaticNumInfo(QVariantMap info){
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString queryInfo;
    if(!info["EquName"].isNull()){
        queryInfo+=QString("and EquName like '%1' ").arg("%"+info["EquName"].toString()+"%");
    }
    if(!info["Ip"].isNull()){
        queryInfo+=QString("and Ip like '%1' ").arg("%"+info["Ip"].toString()+"%");
    }
    if(!info["RunningTimeBegin"].isNull()){
        queryInfo+=QString("and RunningTime >= '%1' ").arg(info["RunningTimeBegin"].toString());
    }
    if(!info["RunningTimeEnd"].isNull()){
        queryInfo+=QString("and RunningTime <= '%1' ").arg(info["RunningTimeEnd"].toString());
    }
    if(!info["TestTimeBegin"].isNull()){
        queryInfo+=QString("and TestTime >= '%1' ").arg(info["TestTimeBegin"].toString());
    }
    if(!info["TestTimeEnd"].isNull()){
        queryInfo+=QString("and TestTime <= '%1' ").arg(info["TestTimeEnd"].toString());
    }
    if(!info["ExpNumBegin"].isNull()){
        queryInfo+=QString("and ExpNum >= '%1' ").arg(info["ExpNumBegin"].toString());
    }
    if(!info["ExpNumEnd"].isNull()){
        queryInfo+=QString("and ExpNum <= '%1' ").arg(info["ExpNumEnd"].toString());
    }
    if(!info["TestSampleNumBegin"].isNull()){
        queryInfo+=QString("and TestSampleNum >= '%1' ").arg(info["TestSampleNumBegin"].toString());
    }
    if(!info["TestSampleNumEnd"].isNull()){
        queryInfo+=QString("and TestSampleNum <= '%1' ").arg(info["TestSampleNumEnd"].toString());
    }

    QString sql=QString("Select e.EquName,Case When e.EquConnectType=0 Then Ip else SerialPort end as Ip,"
                        "s.RunningTime,s.TestTime,s.ExpNum,s.TestSampleNum "
                        "From StaticNumInfo s "
                        "Join EquipmentInfo e on s.EquGuid=e.Id "
                        "Where 1=1 %1").arg(queryInfo);
    query.exec(sql);
    QVariantList result;
    while (query.next()) {
        QVariantMap map;
        for (int i = 0; i < query.record().count(); ++i) {
            if(query.record().fieldName(i)=="RunningTime" || query.record().fieldName(i)=="TestTime"){
                map[query.record().fieldName(i)] =QString::number(query.value(i).toDouble(),'f',1);
            }else{
                map[query.record().fieldName(i)] = query.value(i);
            }
        }
        result.append(map);
    }
    return  result;
}

Q_INVOKABLE int SetUp::checkPassWord(QString passWord){
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);

    QString checkWord;
    QString sql="Select PassWord From IsFirstStartUp";
    query.exec(sql);
    while (query.next()) {
        checkWord=query.value("PassWord").toString();
    }
    return  passWord==checkWord;
}

Q_INVOKABLE int SetUp::editPassWord(QString passWord){
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString sql=QString("Update IsFirstStartUp Set PassWord='%1'").arg(passWord);
    return query.exec(sql)?1:0;
}

// tab3下拉选读取
Q_INVOKABLE QStringList SetUp::tab3comread(){
    QStringList val;
    val<<"基因分析仪"<<"试剂盒类型"<<"体系(μl)";
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString sql="Select Name From ItemCode where DicCode='15'";
    query.exec(sql);
    while (query.next()) {
        QString info=query.value("Name").toString()+"试剂盒";
        val<<info;
    }
    return  val;
}

// 设备名称前端获取
Q_INVOKABLE QStringList SetUp::getEq(){
    QStringList val;
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString sql="Select EquName from EquipmentInfo";
    query.exec(sql);
    while (query.next()) {
        QString name=query.value("EquName").toString();
        val.append(name);
    }
    return val;
}

// 实验名称获取
Q_INVOKABLE QStringList SetUp::getExename(QString name){
    qDebug() << name;
    QStringList val;
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString sql=QString("Select Id from EquipmentInfo where EquName='%1'").arg(name);
    query.exec(sql);
    QString id;
    while (query.next()) {
        id=query.value("Id").toString();
    }
    sql=QString("Select ExperimentName from ExperimentInfo where EquId = '%1'").arg(id);
    query.exec(sql);
    while (query.next()) {
        QString name=query.value("ExperimentName").toString();
        val<<name;
    }
    qDebug() << val;
    return val;
}

// 基因分析仪名称获取
Q_INVOKABLE QStringList SetUp::jyname(){
    QStringList alldata;
    alldata.append("全部");
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString sql="Select Name From ItemCode Where DicCode=14 and IsDefault = '1' ";
    query.exec(sql);
    while (query.next()) {
        alldata.append(query.value("Name").toString());
    }
    sql="Select Name From ItemCode Where DicCode=14 and IsDefault = '0' ";
    query.exec(sql);
    while (query.next()) {
        alldata.append(query.value("Name").toString());
    }
    return alldata;
}

// 试剂盒类型获取
Q_INVOKABLE QStringList SetUp::sjhtypename(){
    QStringList alldata;
    alldata.append("全部");
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString sql="Select Name From ItemCode Where DicCode=15 and IsDefault = '1' ";
    query.exec(sql);
    while (query.next()) {
        alldata.append(query.value("Name").toString());
    }
    sql="Select Name From ItemCode Where DicCode=15 and IsDefault = '0' ";
    query.exec(sql);
    while (query.next()) {
        alldata.append(query.value("Name").toString());
    }
    return alldata;
}

// 体系
Q_INVOKABLE QStringList SetUp::txname(){
    QStringList alldata;
    alldata.append("全部");
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString sql="Select Name From ItemCode Where DicCode=19 and IsDefault = '1' ";
    query.exec(sql);
    while (query.next()) {
        alldata.append(query.value("Name").toString());
    }
    sql="Select Name From ItemCode Where DicCode=19 and IsDefault = '0' ";
    query.exec(sql);
    while (query.next()) {
        alldata.append(query.value("Name").toString());
    }
    return alldata;
}
// 试剂盒名称
QStringList SetUp::sjhmcname(QString type)
{
    QString id;
    QStringList alldata;
    alldata.append("全部");
    QSqlDatabase db = dbHelper::openConnection();
    QSqlQuery query(db);
    QString sql=QString("Select Code From ItemCode Where DicCode=15 and Name = '%1' ").arg(type);
    query.exec(sql);
    while (query.next()) {
        id=query.value("Code").toString();
    }
    sql=QString("Select Name From ItemCode Where DicCode='%1' and IsDefault = '1' ").arg(id);
    query.exec(sql);
    while (query.next()) {
        alldata.append(query.value("Name").toString());
    }
    sql=QString("Select Name From ItemCode Where DicCode='%1' and IsDefault = '0' ").arg(id);
    query.exec(sql);
    while (query.next()) {
        alldata.append(query.value("Name").toString());
    }
    return alldata;
}
